﻿CREATE function AvailableInventory( @ProductID int, @LocationID int ) 
returns int
as
begin
	declare @qty int
	declare @AutoBuild bit
	select @qty = T2.Qty, @AutoBuild = T1.AutoBuild
	from tblProduct T1
		join ( select ProductID, SUM(Qty) as Qty, LocationID from tblInventory group by ProductID, LocationID ) as T2 on T1.ProductID = T2.ProductID
	where T1.ProductID = @ProductID
		and T2.LocationID = @LocationID

	if @AutoBuild = 1 
		select @qty = @qty + min(T2.Qty / T1.Qty)
			from dbo.ProductBuildPlan(@ProductID) as T1
				join ( select ProductID, SUM(Qty) as Qty, LocationID from tblInventory group by ProductID, LocationID ) as T2 on T1.ProductID = T2.ProductID
			where T2.LocationID = @LocationID
	return @qty 
end